package com.template.app.common.service;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

/**
 * Created by hanxq
 * Date: 1/1/17.
 */
public class XlsService {

    private Logger log = Logger.getLogger("XlsService");
    
    public List<List<Object>> getXlsData(MultipartFile file){
    	String fileName = file.getOriginalFilename();
    	String type = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
    	if("xls".equals(type)){
    		return getXlsData_03(file);
    	}else if("xlsx".equals(type)){
    		return getXlsData_07(file);
    	}else{
    		return null;
    	}
    }
    
    

    public List<List<Object>> getXlsData_03(MultipartFile file){
        List<List<Object>> result = new ArrayList<List<Object>>();
        try {
            InputStream is = file.getInputStream();

            HSSFWorkbook tempBook = new HSSFWorkbook(is);
            HSSFSheet sheet = tempBook.getSheetAt(0);
            int recordLineNum = sheet.getLastRowNum();

            if(false){
                throw new Exception("数据大于30000，请分批导入！");
            }else{
                List list = new ArrayList();
                for (int i = 1; i <= recordLineNum; i++) {
                    ArrayList al = new ArrayList();
                    HSSFRow row = sheet.getRow(i);
                    for (int j = 0; j < 10; j++) {
                        HSSFCell cell = row.getCell((short) j);
                        if (cell == null) {
                            al.add("");
                        } else {
                            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                al.add(cell.getStringCellValue());
                            } else  if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
                                long num = (long) cell
                                        .getNumericCellValue();
                                al.add(String.valueOf(num));
                            }else{
                                al.add("");
                            }
                        }

                    }

                    result.add(al);
                }
                log.info("读取数据成功！条数为：" + list.size());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    
    
    public List<List<Object>> getXlsData_07(MultipartFile file){


        List<List<Object>> result = new ArrayList<List<Object>>();
        try {
            InputStream is = file.getInputStream();
            XSSFWorkbook tempBook = new XSSFWorkbook(is);
            XSSFSheet sheet = tempBook.getSheetAt(0);
            int recordLineNum = sheet.getLastRowNum();
            if(false){
                throw new Exception("数据大于30000，请分批导入！");
            }else{
                List list = new ArrayList();
                for (int i = 1; i <= recordLineNum; i++) {
                    ArrayList al = new ArrayList();
                    XSSFRow row = sheet.getRow(i);
                    for (int j = 0; j < 10; j++) {
                       XSSFCell cell = row.getCell((short) j);
                        if (cell == null) {
                            al.add("");
                        } else {
                            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                al.add(cell.getStringCellValue());
                            } else  if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
                                long num = (long) cell
                                        .getNumericCellValue();
                                al.add(String.valueOf(num));
                            }else{
                                al.add("");
                            }
                        }

                    }

                    result.add(al);
                }
                log.info("读取数据成功！条数为：" + list.size());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
}
